import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.Date;

import org.junit.Test;

/**
 * @Title: IntergerityCalculation.java
 * @Package
 * @Description: 计算简历完整度
 * @author zhangzhenhua
 * @date 2016年9月30日 上午10:57:23
 * @version V1.0
 */

/*
 * 思路 ：先查询表cv表得到candidateId ,根据candidateId同时查询 cv_basic
 * cv_education,cv_job,cv_project,cv_skill ,cv_medal,cv_paper,cv_patent
 * 
 */
public class IntegrityCalculation {

	/* 测试数据库 */
	private final static String url = "jdbc:mysql://localhost:3306/123";
	private final static String user = "root";
	private final static String password = "123456";

	/* 远程数据库 */
	private final static String driver = "com.mysql.jdbc.Driver";
	private final static String url2 = "jdbc:mysql://123.57.35.10:3306/hunter2";
	private final static String user2 = "root";
	private final static String password2 = "hunter";
	private static Connection con;
	private static PreparedStatement ps;
	private static ResultSet rs;

	private static PreparedStatement ps2;
	private static ResultSet rs2;

	/* 查询数据库大小 */
	public static long low;
	public static long high;
	public static final String sqlDBSizeLow = "select id  from cv_basic  order by id limit 1";
	public static final String sqlDBSizeHigh = "select id from cv_basic order by id DESC limit 1";/* 31 */

	/* 计数器 */
	public static int count_update = 0, count_total = 0;
	public static int integrityCount = 0;
	public static Double integrity = 0.00;
	public static DecimalFormat df = new DecimalFormat("#0.00");

	/* basic表 */
	public static int id;
	public static int candidateid;
	public static Date create_time;
	public static Date update_time;
	public static int process_stage;
	public static String name;
	public static String usedname;
	public static String englishname;
	public static int gender;
	public static int cardtype;
	public static String idcard;
	public static String company;
	public static String title;
	public static String city;
	public static String birth_place;
	public static String phone;
	public static String mobile;
	public static String email;
	public static String age;
	public static String birthday;
	public static int marriage;
	public static String major;
	public static String degree;
	public static String school;
	public static String jobtime;
	public static String height;
	public static String weight;
	public static String BWH;
	public static String nationality;
	public static String country;
	public static String health;
	public static String politics;
	public static int job_status;
	public static String current_salary;
	public static int expected_salary;
	public static String expected_city;
	public static String expected_job;
	public static String weixin;
	public static String qq;
	public static String linkedin;
	public static String dajie;
	public static String weibo;
	public static float score;
	public static String basic_info;
	public static String address;
	public static int trade_id;
	public static int function_id;
	public static String mpc;
	public static String photo_id;
	public static String comment;
	public static String preference;
	public static int flag;
	public static int fileid;
	public static int ownerid;
	public static int company_id;
	public static int job_time;

	public static void main(String[] args) {

		getDatabaseSize();
	}

	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	public static Connection getConnection2() {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url2, user2, password2);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	private static void getDatabaseSize() {
		try {
			/* 查询表t_candidate首位id */
			con = getConnection2();

			ps = con.prepareStatement(sqlDBSizeLow);
			rs = ps.executeQuery();
			while (rs.next()) {
				low = rs.getInt(1);
			}

			/* 查询表t_candidate尾位id */
			ps = con.prepareStatement(sqlDBSizeHigh);

			rs = ps.executeQuery();
			while (rs.next())
				high = rs.getInt(1);

			rs.close();
			ps.close();
			con.close();

			long num = 10000; /* 每次查询量 */
			long size = high - low + 1;
			long frequency = size / num; /* 查询次数 */
			long mod = size % num;
			long begin = low, end;

			System.out.println(low + "---" + high);
			/* times */
			for (int i = 0; i < frequency; i++) {

				try {
					end = begin + num;
					dealwithQueryNull(90010, 100010);
					begin = end + 1;
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

			}

			dealwithQueryNull(begin, high);

			System.out.println("总查询数 ：" + count_total);
			System.out.println("行影响数 ：" + count_update);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/* 查询其他表 */
	@Test
	private static void dealwithQueryNull(long begin, long end) {
		System.out.println(begin + "===" + end);
		String sqlQueryBasic = "select *from cv_basic where id >= " + begin + " and id <= " + end;
		// String sqlQueryBasic = "select *from cv_basic where id = 965";
		String sqlQueryComment, sqlQueryEducation, sqlQueryJob, sqlQueryMedal, sqlQueryPaper, sqlQuerypatent,
				sqlQueryProject, sqlQuerySkill;
		String sqlUpdateCV;

		try {
			con = getConnection2();
			ps = con.prepareStatement(sqlQueryBasic);
			rs = ps.executeQuery();
//			int i = 0;
//			while(rs.next()) System.out.println(++i);;
//			System.exit(0);
			while (rs.next()) {

				try {
					id = rs.getInt("id");
					candidateid = rs.getInt("candidateid");
					// create_time = rs.getDate("create_time");
					// update_time = rs.getDate("update_time");
					// process_stage = rs.getInt("process_stage");
					name = rs.getString("name");
					// usedname = rs.getString("usedname");
					// englishname = rs.getString("englishname");
					gender = rs.getInt("gender");
					// cardtype = rs.getInt("cardtype");
					idcard = rs.getString("idcard");
					company = rs.getString("company");
					title = rs.getString("title");
					city = rs.getString("city");
					birth_place = rs.getString("birth_place");
					phone = rs.getString("phone");
					mobile = rs.getString("mobile");
					email = rs.getString("email");
					age = rs.getString("age");
					birthday = rs.getString("birthday");
					// marriage = rs.getInt("marriage");
					// major = rs.getString("major");
					degree = rs.getString("degree");
					school = rs.getString("school");
					jobtime = rs.getString("jobtime");
					// height = rs.getString("height");
					// weight = rs.getString("weight");
					// BWH = rs.getString("BWH");
					// nationality = rs.getString("nationality");
					// country = rs.getString("country");
					// health = rs.getString("health");
					// politics = rs.getString("politics");
					job_status = rs.getInt("job_status");
					current_salary = rs.getString("current_salary");
					expected_salary = rs.getInt("expected_salary");
					expected_city = rs.getString("expected_city");
					expected_job = rs.getString("expected_job");
					// weixin = rs.getString("weixin");// expected_job
					// qq = rs.getString("qq");
					// linkedin = rs.getString("linkedin");
					// dajie = rs.getString("dajie");
					// weibo = rs.getString("weibo");
					// score = rs.getInt("score");
					// basic_info = rs.getString("basic_info");
					// address = rs.getString("address");
					trade_id = rs.getInt("trade_id");
					function_id = rs.getInt("function_id");
					// mpc = rs.getString("mpc");
					// photo_id = rs.getString("photo_id");
					// comment = rs.getString("comment");
					// preference = rs.getString("preference");
					// flag = rs.getInt("flag");
					// fileid = rs.getInt("fileid");
					// ownerid = rs.getInt("ownerid");
					// company_id = rs.getInt("company_id");
					// job_time = rs.getInt("job_time");

					integrityCount = 0;

					// 必填项 17 36
					if (name != null) {
						integrityCount += 2;
					}

					if (gender != 0) {
						integrityCount += 2;
					}
					if (birthday != null) {
						integrityCount += 2;
					}
					if (degree != null && degree != "0") {
						integrityCount += 2;
					}
					if (mobile != null) {
						integrityCount += 2;
					}
					if (email != null) {
						integrityCount += 2;
					}
					if (birth_place != null) {
						integrityCount += 2;
					}
					if (company != null) {
						integrityCount += 2;
					}
					if (trade_id != 0) {
						integrityCount += 2;
					}
					if (function_id != 0) {
						integrityCount += 2;
					}
					if (title != null) {
						integrityCount += 2;
					}
					if (jobtime != null) {
						integrityCount += 2;
					}

					if (idcard != null) { // 身份证号
						integrityCount += 4;
					}

					// 职业意向
					if (expected_job != null) {
						integrityCount += 2;
					}
					if (expected_city != null) {
						integrityCount += 2;
					}
					if (expected_salary != 0) {
						integrityCount += 2;
					}
					if (job_status != 0) {
						integrityCount += 2;
					}
					sqlQueryComment = "select * from cv_comment where candidateid = " + candidateid;
					sqlQueryEducation = "select * from cv_education where candidateid = " + candidateid;
					sqlQueryJob = "select * from cv_job where candidateid = " + candidateid;
					sqlQueryProject = "select * from cv_project where candidateid = " + candidateid;
					sqlQuerySkill = "select * from cv_skill where candidateid = " + candidateid;
					// 5 32
					if (isExists(sqlQueryComment)) {
						integrityCount += 4;
					}
					if (isExists(sqlQueryEducation)) {
						integrityCount += 8;
					}
					if (isExists(sqlQueryJob)) {
						integrityCount += 8;
					}

					if (isExists(sqlQueryProject)) {
						integrityCount += 8;
					}
					if (isExists(sqlQuerySkill)) {
						integrityCount += 4;
					}
					integrity = Double.parseDouble(df.format((double) integrityCount / 68));

					sqlUpdateCV = "update cv set integrity = " + integrity * 100 + " where id = " + candidateid;
					ps = con.prepareStatement(sqlUpdateCV);
					int line = ps.executeUpdate(); 
					if (line > 0) {
						System.out.println(line+"--"+count_total + "--->" + count_update++ + "--" + id + "--" + integrity * 100);
					}
					System.out.println(line);
					System.out.println(line+"--"+count_total + "--->" + count_update++ + "--" + id + "--" + integrity * 100);
					count_total++;
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

			}
			rs2.close();
			ps2.close();

			rs.close();
			ps.close();
			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/* 查询是否存在 */
	public static boolean isExists(String sql) {
		try {
			ps2 = con.prepareStatement(sql);
			rs2 = ps2.executeQuery();
			while (rs2.next()) {
				return true;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

}
